package cn.edu.cqupt.cxcy.dto;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.StringEscapeUtils;

import cn.edu.cqupt.cxcy.dao.AdminDao;
import cn.edu.cqupt.cxcy.model.Admin;
import cn.edu.cqupt.cxcy.model.Field;
import cn.edu.cqupt.cxcy.model.Fieldtype;
import cn.edu.cqupt.cxcy.model.GuiDirection;
import cn.edu.cqupt.cxcy.model.Investor;
import cn.edu.cqupt.cxcy.model.LoginFlag;
import cn.edu.cqupt.cxcy.model.Member;
import cn.edu.cqupt.cxcy.model.Notice;
import cn.edu.cqupt.cxcy.model.Noticetype;
import cn.edu.cqupt.cxcy.model.ProjecType;
import cn.edu.cqupt.cxcy.model.Project;
import cn.edu.cqupt.cxcy.model.Projectinvest;
import cn.edu.cqupt.cxcy.model.Team;
import cn.edu.cqupt.cxcy.model.Teamunite;
import cn.edu.cqupt.cxcy.model.Tutor;
import cn.edu.cqupt.cxcy.util.Conn;

public class AdminImpl implements AdminDao{

	@Override
	public boolean addTeac(Tutor teac) {
		Connection conn = null;
		PreparedStatement ps = null;
		int flag = 0;
		String sql = "insert into tutor(tu_id, tu_name, tu_loname, tu_tel, tu_explain, tu_type, tu_NO, tu_ctime) values( 0, ?, ?, ?, ?, ?, ?, sysdate());";
		try {
			conn = Conn.getConnect();
			ps =  conn.prepareStatement(sql);
			ps.setString(1, teac.getTu_name());
			ps.setString(2, teac.getTu_loname());
			ps.setString(3, teac.getTu_tel());
			ps.setString(4, teac.getTu_explain());
			ps.setInt(5, teac.getTu_type());
			ps.setString(6, teac.getTu_NO());
			flag = ps.executeUpdate();
		} catch (Exception e1) {
			e1.printStackTrace();
			throw new RuntimeException(e1.getMessage());
		} finally {
			Conn.close( null, ps, conn);
		}
		return flag == 0?false:true;
	}

	@Override
	public void addInve(Investor inve) {
		Connection conn = null;
		PreparedStatement ps = null;
		String sql = "insert into investor( i_id, i_name, i_loname, i_explain, i_addr, i_tel, i_ctime) values( 0, ?, ?, ?, ?, ?, sysdate());";
		try {
			conn = Conn.getConnect();
			ps =  conn.prepareStatement(sql);
			ps.setString(1, inve.getI_name());
			ps.setString(2, inve.getI_loname());
			ps.setString(3, inve.getI_explain());
			ps.setString(4, inve.getI_addr());
			ps.setString(5, inve.getI_tel());
			ps.executeUpdate();
		} catch (Exception e1) {
			e1.printStackTrace();
			throw new RuntimeException(e1.getMessage());
		} finally {
			Conn.close( null, ps, conn);
		}
	}

	@Override
	public List<Object> cDList( int count, int page, int type, int status) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuilder str = new StringBuilder("select * from field f left join fieldType ft on ft.ft_id=f.ft_id ");
		if(type != 255 || status !=255) {
			str.append(" where ");
			if(type != 255) {
				str.append(" f.ft_id=");
				str.append(type);
			}
			if(type != 255 && status !=255) {
				str.append(" and ");
			}
			if(status !=255){
				str.append("f.f_status=");
				str.append(status);
			}
		}
		str.append(" ORDER BY f.f_asktime desc ");
		if(page != 0) {
			str.append(" limit ");
			str.append((page-1)*count);
			str.append(",");
			str.append(count);
		}
		List<Object> lists = new ArrayList<>();
		Field field = null;
		Fieldtype fieldtype = null;
		List<Object> list = null;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(str.toString());
			rs = ps.executeQuery();
			while(rs.next()) {
				list = new ArrayList<>();
				field = new Field(
						rs.getInt("f_id"),
						rs.getString("f_peoper"),
						rs.getString("f_tel"),
						rs.getInt("ft_id"),
						rs.getDate("f_asktime"),
						rs.getDate("f_stardate"),
						rs.getString("f_user"),
						rs.getTime("f_startime"),
						rs.getTime("f_endtime"),
						rs.getString("f_explain"),
						rs.getInt("f_status"),
						rs.getString("f_remarks"));
				fieldtype = new Fieldtype(
						rs.getInt("ft_id"),
						rs.getString("ft_name"),
						rs.getString("ft_explain"));
				list.add(field);
				list.add(fieldtype);
				lists.add(list);

			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(rs, ps, conn);
		}
		return lists;
	}

	@Override
	public void updateCd(int id, int rs,String remarks) {
		Connection conn = null;
		PreparedStatement ps = null;
		String sql = "update field set f_status=?,f_remarks=? where f_id=?;";
		try {
			conn = Conn.getConnect();
			ps =  conn.prepareStatement(sql);
			ps.setInt(1, rs);
			ps.setString(2, remarks);
			ps.setInt(3, id);
			ps.executeUpdate();
		} catch (Exception e1) {
			e1.printStackTrace();
			throw new RuntimeException(e1.getMessage());
		} finally {
			Conn.close( null, ps, conn);
		}
	}

	@Override
	public void addNews(Notice notice) {
		Connection conn = null;
		PreparedStatement ps = null;
		String sql = "insert into notice( n_id, n_title, n_content, n_ctime, nt_id, a_id, n_from) values( 0, ?, ?, sysdate(), ?, ?, ?);";
		try {
			conn = Conn.getConnect();
			ps =  conn.prepareStatement(sql);
			ps.setString(1, notice.getN_title());
			ps.setString(2, notice.getN_content());
			ps.setInt(3, notice.getNt_id());
			ps.setInt(4, notice.getA_id());
			ps.setString(5, notice.getN_from());
			ps.executeUpdate();
		} catch (Exception e1) {
			e1.printStackTrace();
			throw new RuntimeException(e1.getMessage());
		} finally {
			Conn.close( null, ps, conn);
		}
	}

	@Override
	public boolean delNews(int id) {
		String sql = "delete from notice where n_id=?";
		Connection conn = null;
		PreparedStatement ps = null;
		int flag = 0;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(sql);
			ps.setInt( 1, id);
			flag = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Conn.close(null, ps, conn);
		}
		return flag == 0 ? false : true;
	}

	@Override
	public boolean isHaveNews(int id) {
		String sql = "select * from notice where n_id=?";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		boolean flag = false;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(sql);
			ps.setInt( 1, id);
			rs = ps.executeQuery();
			if(rs != null && rs.next()) {
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Conn.close(rs, ps, conn);
		}
		return flag;
	}

	@Override
	public boolean addTeam(Team team) {
		Connection conn = null;
		PreparedStatement ps = null;
		int flag = 0;
		String sql = "insert into team( te_id, te_name, te_loname, te_passw, te_ctime) values(0, ?, ?, ?, sysdate());";
		try {
			conn = Conn.getConnect();
			ps =  conn.prepareStatement(sql);
			ps.setString(1, team.getTe_name());
			ps.setString(2, team.getTe_loname());
			ps.setString(3, team.getTe_passw());
			flag = ps.executeUpdate();
		} catch (Exception e1) {
			e1.printStackTrace();
			throw new RuntimeException(e1.getMessage());
		} finally {
			Conn.close( null, ps, conn);
		}
		return flag ==0?false:true;
	}

	@Override
	public String getStatus(String mail) {
		List<String> sql = new ArrayList<>();
		//sql.add("select a_check as check from admin where a_loname=?;");
		sql.add("select tu_check as check  from tutor where tu_loname=?;");
		sql.add("select i_check as check  from investor where i_loname=?;");
		sql.add("select te_check as check  from team set where te_loname=?;");
		String flag = "";
		for(int i=2; i>-1; i--) {
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				conn = Conn.getConnect();
				ps = conn.prepareStatement(sql.get(2-i));
				ps.setString(1, mail);
				rs = ps.executeQuery();
				while(rs.next()) {
					flag = rs.getString("check");
				}
				if(!flag.equals("")) {
					break;
				}
			} catch (SQLException e) {
				e.printStackTrace();
				throw new RuntimeException(e.getMessage());
			} finally {
				Conn.close(null, ps, conn);
			}
		}
		return flag;
	}

	@Override
	public boolean updatePass(String mail, String passw) {
		List<String> sql = new ArrayList<>();
		sql.add("update admin set a_passw=? where a_loname=?;");
		sql.add("update tutor set tu_passw=? where tu_loname=?;");
		sql.add("update investor set i_passw=? where i_loname=?;");
		sql.add("update team set te_passw=? where te_loname=?;");
		int flag = 0;
		for(int i=3; i>-1; i--) {
			Connection conn = null;
			PreparedStatement ps = null;
			try {
				conn = Conn.getConnect();
				ps = conn.prepareStatement(sql.get(3-i));
				ps.setString(1, passw);
				ps.setString(2, mail);
				flag = ps.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
				throw new RuntimeException(e.getMessage());
			} finally {
				Conn.close(null, ps, conn);
			}
		}
		return flag == 0?false:true;
	}

	@Override
	public List<Object> tutorList(int type, int page, int count) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuilder str = new StringBuilder("select * from tutor t left join guiDirection gd on t.gd_id=gd.gd_id ");
		if(type == 1) {
			str.append(" left join teamTutor tt on tt.tu_id=t.tu_id where tt.tt_teamcheck=1 and tt.tt_tutorcheck=1 ");
		} else if(type == 2) {
			str.append(" left join teamTutor tt on tt.tu_id=t.tu_id where tt.tt_teamcheck=0 ");
		} 
		str.append(" ORDER BY tu_ctime desc limit ");
		str.append((page-1) * count);
		str.append(",");
		str.append(count);
		List<Object> tutors = new ArrayList<>();
		List<Object> list = null;
		Tutor tutor = null;
		GuiDirection gd = null;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(str.toString());
			rs = ps.executeQuery();
			while(rs.next()) {
				list = new ArrayList<>();
				tutor = new Tutor(
						rs.getInt("tu_id"),
						rs.getString("tu_name"),
						rs.getString("tu_logo"),
						rs.getString("tu_loname"),
						null,
						rs.getString("tu_tel"),
						rs.getString("tu_explain"),
						rs.getDate("tu_ctime"),
						rs.getInt("gd_id"));
				tutor.setTu_type(rs.getInt("tu_type"));
				gd = new GuiDirection(
						rs.getInt("gd_id"),
						rs.getString("gd_name"),
						rs.getString("gd_explain"));
				list.add(tutor);
				list.add(gd);
				tutors.add(list);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(rs, ps, conn);
		}
		return tutors;
	}

	@Override
	public List<Object> investorList(int star, int count) {
		String sql = "";
		if(count == 0) {
			sql = "select * from investor ORDER BY i_ctime;";
		} else {
			sql = "select * from investor ORDER BY i_ctime desc limit " + star +"," + count + ";";
		}
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Object> Investors = new ArrayList<>();
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				Investor inve = new Investor(
						rs.getInt("i_id"),
						rs.getString("i_name"),
						rs.getString("i_logo"),
						rs.getString("i_loname"),
						rs.getString("i_passw"),
						rs.getString("i_explain"),
						rs.getDate("i_ctime"),
						rs.getString("i_tel"),
						rs.getDouble("i_amount"));
				Investors.add(inve);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(rs, ps, conn);
		}
		return Investors;
	}

	@Override
	public List<Object> noticeList(int page, int count, int type) {
		StringBuilder str = new StringBuilder("select * from notice n left join admin a on a.a_id=n.a_id left join noticeType nt on nt.nt_id=n.nt_id ");
		if(type != 255) {
			str.append("where n.nt_id=");
			str.append(type);
		}
		str.append(" ORDER BY n_ctime desc limit ");
		str.append((page-1) * count);
		str.append(",");
		str.append(count);
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Object> lists = new ArrayList<>();
		List<Object> list = null;
		Notice notice = null;
		Admin admin = null;
		Noticetype noticetype = null;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(str.toString());
			rs = ps.executeQuery();

			while(rs.next()) {
				list = new ArrayList<>();
				notice = new Notice(
						rs.getInt("n_id"),
						rs.getString("n_title"),
						rs.getString("n_content"),
						rs.getInt("n_view"),
						rs.getDate("n_ctime"),
						rs.getInt("nt_id"),
						rs.getInt("a_id"),
						rs.getString("n_filename"),
						rs.getString("n_filepath"),
						rs.getString("n_filexplain"));
				admin = new Admin(
						rs.getInt("a_id"),
						rs.getString("a_name"),
						rs.getString("a_loname"),
						null,
						rs.getString("a_explain"),
						rs.getString("a_logo"));
				noticetype = new Noticetype(
						rs.getInt("nt_id"),
						rs.getString("nt_name"),
						rs.getString("nt_explain"));
				list.add(notice);
				list.add(admin);
				list.add(noticetype);
				lists.add(list);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(rs, ps, conn);
		}
		return lists;
	}

	@Override
	public Investor invenfo(String loname) {
		String sql = "select * from investor where i_loname=?";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Investor inve = null;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(sql);
			ps.setString(1, loname);
			rs = ps.executeQuery();
			while(rs.next()) {
				inve = new Investor(
						rs.getInt("i_id"),
						rs.getString("i_name"),
						rs.getString("i_logo"),
						rs.getString("i_loname"),
						rs.getString("i_passw"),
						rs.getString("i_explain"),
						rs.getDate("i_ctime"),
						rs.getString("i_tel"),
						rs.getDouble("i_amount"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(rs, ps, conn);
		}
		return inve;
	}

	@Override
	public Investor invenfo(int id) {
		String sql = "select * from investor where i_id=?";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Investor inve = null;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while(rs.next()) {
				inve = new Investor(
						rs.getInt("i_id"),
						rs.getString("i_name"),
						rs.getString("i_logo"),
						rs.getString("i_loname"),
						rs.getString("i_passw"),
						rs.getString("i_explain"),
						rs.getDate("i_ctime"),
						rs.getString("i_tel"),
						rs.getDouble("i_amount"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(rs, ps, conn);
		}
		return inve;
	}

	@Override
	public Team teamInfo(String loname) {
		String sql = "select * from team where te_loname=?";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Team team = null;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(sql);
			ps.setString(1, loname);
			rs = ps.executeQuery();
			while(rs.next()) {
				team = new Team(
						rs.getString("te_name"),
						rs.getString("te_logo"),
						rs.getString("te_loname"),
						rs.getString("te_passw"),
						rs.getDate("te_ctime"),
						rs.getString("te_explain"),
						rs.getInt("te_id"),
						rs.getString("te_check"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(rs, ps, conn);
		}
		return team;
	}

	@Override
	public Team teamInfo(int id) {
		String sql = "select * from team where te_id=?";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Team team = null;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while(rs.next()) {
				team = new Team(
						rs.getString("te_name"),
						rs.getString("te_logo"),
						rs.getString("te_loname"),
						rs.getString("te_passw"),
						rs.getDate("te_ctime"),
						rs.getString("te_explain"),
						rs.getInt("te_id"),
						rs.getString("te_check"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(rs, ps, conn);
		}
		return team;
	}

	@Override
	public Tutor tutorInfo(String loname) {
		String sql = "select * from tutor where tu_loname=?";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Tutor tutor = null;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(sql);
			ps.setString(1, loname);
			rs = ps.executeQuery();
			while(rs.next()) {
				tutor = new Tutor(
						rs.getInt("tu_id"),
						rs.getString("tu_name"),
						rs.getString("tu_logo"),
						rs.getString("tu_loname"),
						rs.getString("tu_passw"),
						rs.getString("tu_tel"),
						rs.getString("tu_explain"),
						rs.getDate("tu_ctime"),
						rs.getInt("gd_id"));
				tutor.setTu_type(rs.getInt("tu_type"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(rs, ps, conn);
		}
		return tutor;
	}

	@Override
	public List<Object> tutorInfo(int id) {
		String sql = "select * from tutor t left join guiDirection gd on gd.gd_id=t.gd_id where t.tu_id=?";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Object> lists = new ArrayList<>();
		List<Object> list = null;
		Tutor tutor = null;
		GuiDirection gd = null;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while(rs.next()) {
				list = new ArrayList<>();
				tutor = new Tutor(
						rs.getInt("tu_id"),
						rs.getString("tu_name"),
						rs.getString("tu_logo"),
						rs.getString("tu_loname"),
						rs.getString("tu_passw"),
						rs.getString("tu_tel"),
						rs.getString("tu_explain"),
						rs.getDate("tu_ctime"),
						rs.getInt("gd_id"));
				tutor.setTu_type(rs.getInt("tu_type"));

				gd = new GuiDirection();
				gd.setGd_name(rs.getString("gd_name"));

				list.add(tutor);
				list.add(gd);
				lists.add(list);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(rs, ps, conn);
		}
		return lists;
	}

	@Override
	public boolean delTeam(int id) {
		String sql = "delete from team where te_id=?;";
		int flag = 0;
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			flag = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(null, ps, conn);
		}
		return flag == 0?false:true;
	}

	@Override
	public boolean delTutor(int id) {
		String sql = "delete from tutor where tu_id=?;";
		int flag = 0;
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			flag = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(null, ps, conn);
		}
		return flag == 0?false:true;
	}

	@Override
	public boolean delInve(int id) {
		String sql = "delete from investor where i_id=?;";
		int flag = 0;
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			flag = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(null, ps, conn);
		}
		return flag == 0?false:true;
	}

	@Override
	public List<Object> teamList(int page, int count, int type) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuilder str = new StringBuilder("select * from team ");
		if(type == 1) {
			str.append(" t left join teamTutor tt on tt.te_id=t.te_id where tt.tt_teamcheck=1 and tt.tt_tutorcheck=1 ");
		} else if(type == 2){
			str.append(" t left join teamTutor tt on tt.te_id=t.te_id where tt.tt_tutorcheck=0 ");
		}
		str.append(" ORDER BY te_ctime desc limit ");
		str.append((page-1) * count);
		str.append(",");
		str.append(count);
		List<Object> list = new ArrayList<>();
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(str.toString());
			rs = ps.executeQuery();
			while(rs.next()) {
				Team team = new Team(
						rs.getString("te_name"),
						rs.getString("te_logo"),
						rs.getString("te_loname"),
						rs.getString("te_passw"),
						rs.getDate("te_ctime"),
						rs.getString("te_explain"),
						rs.getInt("te_id"),
						rs.getString("te_check"));
				list.add(team);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(rs, ps, conn);
		}
		return list;
	}

	@Override
	public int countCd(String type, int status) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int count = 0;
		StringBuilder str = new StringBuilder("SELECT count(f_id) as count from field ");
		if(type != null || status !=255) {
			str.append(" where ");
			if(type != null) {
				str.append(" ft_name='");
				str.append(type);
				str.append("' ");
			} 
			if(type != null && status !=255) {
				str.append(" and ");
			}
			if(status !=255){
				str.append(" f_status=");
				str.append(status);
			}
		}
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(str.toString());
			rs = ps.executeQuery();
			while(rs.next()) {
				count = rs.getInt("count");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(rs, ps, conn);
		}
		return count;
	}

	@Override
	public int countInve() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int count = 0;
		StringBuilder str = new StringBuilder("SELECT count(i_id) as count from investor ");
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(str.toString());
			rs = ps.executeQuery();
			while(rs.next()) {
				count = rs.getInt("count");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(rs, ps, conn);
		}
		return count;
	}

	@Override
	public int countTutor(int type) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int count = 1;
		StringBuilder str = new StringBuilder("SELECT count(t.tu_id) as count from tutor t ");
		if(type == 1) {
			str.append(" left join teamTutor tt on tt.tu_id=t.tu_id where tt.tt_teamcheck=1 and tt.tt_tutorcheck=1 ");
		} else if(type == 2) {
			str.append(" left join teamTutor tt on tt.tu_id=t.tu_id where tt.tt_teamcheck=0 ");
		} 
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(str.toString());
			rs = ps.executeQuery();
			while(rs.next()) {
				count = rs.getInt("count");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(rs, ps, conn);
		}
		return count;
	}

	@Override
	public int countTeam(int type) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int count = 0;
		StringBuilder str = new StringBuilder("SELECT count(t.te_id) as count from team  t ");
		if(type == 1) {
			str.append(" left join teamTutor tt on tt.te_id=t.te_id where tt.tt_teamcheck=1 and tt.tt_tutorcheck=1 ");
		} else if(type == 2) {
			str.append(" left join teamTutor tt on tt.te_id=t.te_id where tt.tt_tutorcheck=0");
		} 
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(str.toString());
			rs = ps.executeQuery();
			while(rs.next()) {
				count = rs.getInt("count");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(rs, ps, conn);
		}
		return count;
	}

	@Override
	public int countNotice(int type) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int count = 0;
		StringBuilder str = new StringBuilder("SELECT count(n_id) as count from notice ");
		if(type != 255) {
			str.append(" where nt_id=");
			str.append(type);
			str.append(";");
		} 
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(str.toString());
			rs = ps.executeQuery();
			while(rs.next()) {
				count = rs.getInt("count");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(rs, ps, conn);
		}
		return count;
	}

	@Override
	public int pointCdyy() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int count = 0;
		String str = new String("SELECT count(f_id) as count from field where f_status=0;");
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(str);
			rs = ps.executeQuery();
			while(rs.next()) {
				count = rs.getInt("count");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(rs, ps, conn);
		}
		return count;
	}

	@Override
	public int pointPro() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int count = 0;
		String str = new String("SELECT count(p_id) as count from project where p_status=0;");
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(str);
			rs = ps.executeQuery();
			while(rs.next()) {
				count = rs.getInt("count");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(rs, ps, conn);
		}
		return count;
	}

	@Override
	public List<Object> proList(int status, int page, int count) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuilder str = new StringBuilder("SELECT * from project p ");
		str.append(" left join team t on t.te_id=p.te_id left join projecType pt on p.pt_id=pt.pt_id ");
		if(status != 255) {
			str.append(" where p_status=");
			str.append(status);
		}  
		str.append(" ORDER BY p_ctime desc limit ");
		str.append((page-1) * count);
		str.append(",");
		str.append(count);
		List<Object> lists = new ArrayList<>();
		List<Object> list  = null;
		Project pro = null;
		Team team = null;
		ProjecType pt  = null;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(str.toString());
			rs = ps.executeQuery();
			while(rs.next()) {
				list = new ArrayList<>();
				pro = new Project(
						rs.getInt("p_id"),
						rs.getString("p_name"),
						rs.getString("p_logo"),
						rs.getDate("p_ctime"),
						rs.getDate("p_ltime"),
						rs.getInt("p_status"),
						rs.getInt("p_progress"),
						rs.getInt("p_view"),
						StringEscapeUtils.escapeHtml(rs.getString("p_content")),
						rs.getString("p_explain"),
						rs.getInt("te_id"),
						rs.getInt("pt_id"),
						rs.getString("p_remarks"),
						rs.getString("p_filename"),
						rs.getString("p_filepath"));

				team = new Team(
						rs.getString("te_name"),
						rs.getString("te_logo"),
						rs.getString("te_loname"),
						rs.getString("te_passw"),
						rs.getDate("te_ctime"),
						rs.getString("te_explain"),
						rs.getInt("te_id"),
						rs.getString("te_check"));

				pt =  new ProjecType(
						rs.getInt("pt_id"),
						rs.getString("pt_name"),
						rs.getString("pt_explain"));
				list.add(pro);
				list.add(team);
				list.add(pt);
				lists.add(list);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(rs, ps, conn);
		}
		return lists;
	}

	@Override
	public int countPro(int status) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int count = 0;
		StringBuilder str = new StringBuilder("SELECT count(p_id) as count from project ");
		if(status != 255) {
			str.append(" where p_status=");
			str.append(status);
		} 
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(str.toString());
			rs = ps.executeQuery();
			while(rs.next()) {
				count = rs.getInt("count");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(rs, ps, conn);
		}
		return count;
	}

	@Override
	public List<Noticetype> noticeType() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuilder str = new StringBuilder("SELECT * from noticeType;");
		List<Noticetype> list = new ArrayList<>();
		Noticetype nt = null;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(str.toString());
			rs = ps.executeQuery();
			while(rs.next()) {
				nt = new Noticetype(
						rs.getInt("nt_id"),
						rs.getString("nt_name"),
						rs.getString("nt_explain"));
				list.add(nt);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(rs, ps, conn);
		}
		return list;
	}

	@Override
	public List<Fieldtype> fieldType() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuilder str = new StringBuilder("SELECT * from fieldType;");
		List<Fieldtype> list = new ArrayList<>();
		Fieldtype ft = null;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(str.toString());
			rs = ps.executeQuery();
			while(rs.next()) {
				ft = new Fieldtype(
						rs.getInt("ft_id"),
						rs.getString("ft_name"),
						rs.getString("ft_explain"));
				list.add(ft);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(rs, ps, conn);
		}
		return list;
	}

	@Override
	public List<Object> caleList(Date s, int type) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuilder str = new StringBuilder("select * from field f left join fieldType ft on ft.ft_id=f.ft_id where f.f_stardate=?");
		if(type != 255) {
			str.append(" where f.ft_id=");
			str.append(type);
		}
		str.append(" ORDER BY f.f_startime asc;");
		List<Object> lists = new ArrayList<>();
		Field field = null;
		Fieldtype fieldtype = null;
		List<Object> list = null;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(str.toString());
			ps.setDate(1, new java.sql.Date(s.getTime()));
			rs = ps.executeQuery();
			while(rs.next()) {
				list = new ArrayList<>();
				field = new Field(
						rs.getInt("f_id"),
						rs.getString("f_peoper"),
						rs.getString("f_tel"),
						rs.getInt("ft_id"),
						rs.getDate("f_asktime"),
						rs.getDate("f_stardate"),
						rs.getString("f_user"),
						rs.getTime("f_startime"),
						rs.getTime("f_endtime"),
						rs.getString("f_explain"),
						rs.getInt("f_status"),
						rs.getString("f_remarks"));
				fieldtype = new Fieldtype(
						rs.getInt("ft_id"),
						rs.getString("ft_name"),
						rs.getString("ft_explain"));
				list.add(field);
				list.add(fieldtype);
				lists.add(list);

			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(rs, ps, conn);
		}
		return lists;
	}

	@Override
	public List<GuiDirection> guiDirection() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuilder str = new StringBuilder("SELECT * from guiDirection;");
		List<GuiDirection> list = new ArrayList<>();
		GuiDirection gd = null;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(str.toString());
			rs = ps.executeQuery();
			while(rs.next()) {
				gd = new GuiDirection(
						rs.getInt("gd_id"),
						rs.getString("gd_name"),
						rs.getString("gd_explain"));
				list.add(gd);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(rs, ps, conn);
		}
		return list;
	}

	@Override
	public List<ProjecType> projecType() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuilder str = new StringBuilder("SELECT * from projecType;");
		List<ProjecType> list = new ArrayList<>();
		ProjecType pt = null;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(str.toString());
			rs = ps.executeQuery();
			while(rs.next()) {
				pt = new ProjecType(
						rs.getInt("pt_id"),
						rs.getString("pt_name"),
						rs.getString("pt_explain"));
				list.add(pt);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(rs, ps, conn);
		}
		return list;
	}

	@Override
	public List<Object> invePro(int id) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuilder str = new StringBuilder("select * from projectInvest pi left join investor i on i.i_id=pi.i_id left join project p on p.p_id=pi.p_id left join projecType pt on pt.pt_id=p.pt_id where i.i_id=?");
		List<Object> lists = new ArrayList<>();
		List<Object> list  = null;
		Project pro = null;
		Projectinvest pi = null;
		ProjecType pt  = null;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(str.toString());
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while(rs.next()) {
				list = new ArrayList<>();
				pro = new Project(
						rs.getInt("p_id"),
						rs.getString("p_name"),
						rs.getString("p_logo"),
						rs.getDate("p_ctime"),
						rs.getDate("p_ltime"),
						rs.getInt("p_status"),
						rs.getInt("p_progress"),
						rs.getInt("p_view"),
						rs.getString("p_content"),
						rs.getString("p_explain"),
						rs.getInt("te_id"),
						rs.getInt("pt_id"),
						rs.getString("p_remarks"),
						rs.getString("p_filename"),
						rs.getString("p_filepath"));

				pi = new Projectinvest(
						rs.getInt("pi_id"),
						rs.getInt("i_id"),
						rs.getInt("p_id"),
						rs.getDouble("pi_amount"),
						rs.getDate("pi_time"),
						rs.getString("pi_explain"));

				pt =  new ProjecType(
						rs.getInt("pt_id"),
						rs.getString("pt_name"),
						rs.getString("pt_explain"));
				list.add(pro);
				list.add(pt);
				list.add(pi);
				lists.add(list);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(rs, ps, conn);
		}
		return lists;
	}

	@Override
	public List<Object> proInfo(int id) {
		String sql = "select * from project p left join projectInvest pi on p.p_id=pi.p_id left join investor i on i.i_id=pi.i_id left join team te on te.te_id=p.te_id left join projecType pt on pt.pt_id=p.pt_id where p.p_id=?;";
		List<Object> list = new ArrayList<>();//装下面五个的容器
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Project pro = null;
		Projectinvest pi = null;
		Investor in = null;
		Team te = null;
		ProjecType pt = null;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while (rs.next()) {
				pro = new Project();//项目的容器
				pi = new Projectinvest();//项目与投资人关系的容器
				in = new Investor();//投资人的容器
				te = new Team();//团队的容器

				pro.setP_content(rs.getString("P_content"));
				pro.setP_ctime(rs.getDate("P_ctime"));
				pro.setP_explain(rs.getString("P_explain"));
				pro.setP_id(rs.getInt("P_id"));
				pro.setTe_id(rs.getInt("p_view"));
				pro.setTe_id(rs.getInt("p_view"));
				pro.setP_logo(rs.getString("P_logo"));
				pro.setP_ltime(rs.getDate("P_ltime"));
				pro.setP_name(rs.getString("P_name"));
				pro.setP_progress(rs.getInt("P_progress"));
				pro.setP_status(rs.getInt("p_status"));
				pro.setTe_id(rs.getInt("Te_id"));
				pro.setTe_id(rs.getInt("p_view"));

				pi.setI_id(rs.getInt("I_id"));
				pi.setP_id(rs.getInt("P_id"));
				pi.setPi_amount(rs.getDouble("Pi_amount"));
				pi.setPi_explain(rs.getString("Pi_explain"));
				pi.setPi_id(rs.getInt("Pi_id"));
				pi.setPi_time(rs.getDate("Pi_time"));

				in.setI_explain(rs.getString("I_explain"));
				in.setI_id(rs.getInt("I_id"));
				in.setI_logo(rs.getString("I_logo"));
				in.setI_name(rs.getString("I_name"));

				te.setTe_explain(rs.getString("Te_explain"));
				te.setTe_id(rs.getInt("Te_id"));
				te.setTe_logo(rs.getString("Te_logo"));

				pt =  new ProjecType(
						rs.getInt("pt_id"),
						rs.getString("pt_name"),
						rs.getString("pt_explain"));

				list.add(pro);
				list.add(pi);
				list.add(in);
				list.add(te);
				list.add(pt);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Conn.close(rs, null, null);
		}
		return list;
	}

	@Override
	public List<Object> memberInfo(int tid) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuilder str = new StringBuilder("select * from member m left join teamUnite tu on m.m_id=tu.m_id left join team t on t.te_id=tu.te_id where t.te_id=? order by tun_type desc");
		List<Object> lists = new ArrayList<>();
		Member m = null;
		Teamunite tu = new Teamunite();
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(str.toString());
			ps.setInt(1, tid);
			rs = ps.executeQuery();
			while(rs.next()) {
				List<Object> list = new ArrayList<>();
				m = new Member(
						rs.getInt("m_id"),
						rs.getString("m_name"),
						rs.getString("m_sex"),
						rs.getString("m_logo"),
						rs.getDate("m_birth"),
						rs.getString("m_tel"),
						rs.getString("m_no"),
						rs.getString("m_xl"),
						rs.getDate("m_ctime"),
						rs.getString("m_email"),
						rs.getString("m_school"),
						rs.getString("m_college"),
						rs.getString("m_explain"),
						null);
				tu.setTun_type(rs.getString("tun_type"));

				list.add(m);
				list.add(tu);
				lists.add(list);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(rs, ps, conn);
		}
		return lists;
	}

	@Override
	public List<Object> tutorTeam(int id) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuilder str = new StringBuilder("select * from team t left join teamTutor tt on t.te_id=tt.te_id left join tutor tu on tu.tu_id=tt.tu_id where tt_teamcheck=2 and tt_tutorcheck=2 and tu.tu_id=?;");
		List<Object> lists = new ArrayList<>();
		Member m = null;
		Teamunite tu = new Teamunite();
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(str.toString());
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while(rs.next()) {
				List<Object> list = new ArrayList<>();
				m = new Member(
						rs.getInt("m_id"),
						rs.getString("m_name"),
						rs.getString("m_sex"),
						rs.getString("m_logo"),
						rs.getDate("m_birth"),
						rs.getString("m_tel"),
						rs.getString("m_no"),
						rs.getString("m_xl"),
						rs.getDate("m_ctime"),
						rs.getString("m_email"),
						rs.getString("m_school"),
						rs.getString("m_college"),
						rs.getString("m_explain"),
						null);
				tu.setTun_type(rs.getString("tun_type"));

				list.add(m);
				list.add(tu);
				lists.add(list);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(rs, ps, conn);
		}
		return lists;
	}

	@Override
	public Boolean delPro(int id) {
		String sql = "delete from project where p_id=?";
		Connection conn = null;
		PreparedStatement ps = null;
		int flag = 0;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(sql);
			ps.setInt( 1, id);
			flag = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Conn.close(null, ps, conn);
		}
		return flag == 0 ? false : true;
	}

	@Override
	public List<Object> newsInfo(int id) {
		StringBuilder str = new StringBuilder("select * from notice n left join admin a on a.a_id=n.a_id left join noticeType nt on nt.nt_id=n.nt_id where n.n_id=?;");
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Object> lists = new ArrayList<>();
		Notice notice = null;
		Admin admin = null;
		Noticetype noticetype = null;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(str.toString());
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while(rs.next()) {
				notice = new Notice(
						rs.getInt("n_id"),
						rs.getString("n_title"),
						rs.getString("n_content"),
						rs.getInt("n_view"),
						rs.getDate("n_ctime"),
						rs.getInt("nt_id"),
						rs.getInt("a_id"),
						rs.getString("n_filename"),
						rs.getString("n_filepath"),
						rs.getString("n_filexplain"));
				admin = new Admin(
						rs.getInt("a_id"),
						rs.getString("a_name"),
						null,
						null,
						null,
						null);
				noticetype = new Noticetype(
						rs.getInt("nt_id"),
						rs.getString("nt_name"),
						rs.getString("nt_explain"));
				lists.add(notice);
				lists.add(admin);
				lists.add(noticetype);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(rs, ps, conn);
		}
		return lists;
	}

	@Override
	public boolean examPro(int pid, int id, String opin) {
		StringBuilder str = new StringBuilder("update project set p_status=?,p_remarks=? where p_id=?;");
		Connection conn = null;
		PreparedStatement ps = null;
		int flag = 0;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(str.toString());
			ps.setInt(1, id);
			ps.setString(2, opin);
			ps.setInt(3, pid);
			flag = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close( null, ps, conn);
		}
		return flag==0?false:true;
	}

	@Override
	public Boolean isHaveLable_Pros(String lable) {
		String sql = "select * from projecType where pt_name=?";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		boolean flag = false;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(sql);
			ps.setString( 1, lable);
			rs = ps.executeQuery();
			if(rs != null && rs.next()) {
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Conn.close(rs, ps, conn);
		}
		return flag;
	}

	@Override
	public Boolean isHaveLable_News(String lable) {
		String sql = "select * from noticeType where nt_name=?";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		boolean flag = false;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(sql);
			ps.setString( 1, lable);
			rs = ps.executeQuery();
			if(rs != null && rs.next()) {
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Conn.close(rs, ps, conn);
		}
		return flag;
	}

	@Override
	public Boolean isHaveLable_Teac(String lable) {
		String sql = "select * from guiDirection where gd_name=?";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		boolean flag = false;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(sql);
			ps.setString( 1, lable);
			rs = ps.executeQuery();
			if(rs != null && rs.next()) {
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Conn.close(rs, ps, conn);
		}
		return flag;
	}

	@Override
	public Boolean isHaveLable_Cdyy(String lable) {
		String sql = "select * from fieldType where ft_name=?";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		boolean flag = false;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(sql);
			ps.setString( 1, lable);
			rs = ps.executeQuery();
			if(rs != null && rs.next()) {
				flag = true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			Conn.close(rs, ps, conn);
		}
		return flag;
	}

	@Override
	public Boolean addLable_Pros(String lable) {
		Connection conn = null;
		PreparedStatement ps = null;
		int flag = 0;
		String sql = "insert into projecType(pt_id, pt_name) values( 0, ?);";
		try {
			conn = Conn.getConnect();
			ps =  conn.prepareStatement(sql);
			ps.setString(1, lable);
			flag = ps.executeUpdate();
		} catch (Exception e1) {
			e1.printStackTrace();
			throw new RuntimeException(e1.getMessage());
		} finally {
			Conn.close( null, ps, conn);
		}
		return flag == 0?false:true;
	}

	@Override
	public Boolean addLable_News(String lable) {
		Connection conn = null;
		PreparedStatement ps = null;
		int flag = 0;
		String sql = "insert into noticeType(nt_id, nt_name) values( 0, ?);";
		try {
			conn = Conn.getConnect();
			ps =  conn.prepareStatement(sql);
			ps.setString(1, lable);
			flag = ps.executeUpdate();
		} catch (Exception e1) {
			e1.printStackTrace();
			throw new RuntimeException(e1.getMessage());
		} finally {
			Conn.close( null, ps, conn);
		}
		return flag == 0?false:true;
	}

	@Override
	public Boolean addLable_Teac(String lable) {
		Connection conn = null;
		PreparedStatement ps = null;
		int flag = 0;
		String sql = "insert into guiDirection(gd_id, gd_name) values( 0, ?);";
		try {
			conn = Conn.getConnect();
			ps =  conn.prepareStatement(sql);
			ps.setString(1, lable);
			flag = ps.executeUpdate();
		} catch (Exception e1) {
			e1.printStackTrace();
			throw new RuntimeException(e1.getMessage());
		} finally {
			Conn.close( null, ps, conn);
		}
		return flag == 0?false:true;
	}

	@Override
	public Boolean addLable_Cdyy(String lable) {
		Connection conn = null;
		PreparedStatement ps = null;
		int flag = 0;
		String sql = "insert into fieldType(ft_id, ft_name) values( 0, ?);";
		try {
			conn = Conn.getConnect();
			ps =  conn.prepareStatement(sql);
			ps.setString(1, lable);
			flag = ps.executeUpdate();
		} catch (Exception e1) {
			e1.printStackTrace();
			throw new RuntimeException(e1.getMessage());
		} finally {
			Conn.close( null, ps, conn);
		}
		return flag == 0?false:true;
	}

	@Override
	public boolean SessionSelect(String name) {
		boolean status = true;
		String sql = "select * from loginFlag where sys_username=?;";
		for(int i=3; i>-1; i--) {
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				conn = Conn.getConnect();
				ps = conn.prepareStatement(sql);
				ps.setString(1, name);
				rs = ps.executeQuery();
				while(rs.next()) {
					status = rs.getBoolean("sys_flag");
				}
			} catch (SQLException e) {
				e.printStackTrace();
				throw new RuntimeException(e.getMessage());
			} finally {
				Conn.close(rs, ps, conn);
			}
		}
		return status;
	}

	@Override
	public void SessionUpdata(String name, boolean trueorfalse) {
		String sql = "update loginFlag set sys_flag=? where sys_username=?;";
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(sql);
			ps.setBoolean(1, trueorfalse);;
			ps.setString(2, name);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close( null, ps, conn);
		}
	}

	@Override
	public boolean updateSysFlag(LoginFlag loginFlag) {
		String sql = "update loginFlag set sys_flag=?, sys_views=sys_views+1, sys_intime=sysdate(), sys_nip=?, sys_sessionId=? where sys_username=?;";
		int flag = 0;
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(sql);
			ps.setBoolean(1, loginFlag.getSys_flag());
			ps.setString(2, loginFlag.getSys_nip());
			ps.setString(3, loginFlag.getSys_sessionId());
			ps.setString(4, loginFlag.getSys_username());
			flag = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close( null, ps, conn);
		}
		return flag == 0 ? false : true;
	}

	@Override
	public List<Object> noticeListEscapeHtml(int page, int count, int type) {
		StringBuilder str = new StringBuilder("select * from notice n left join admin a on a.a_id=n.a_id left join noticeType nt on nt.nt_id=n.nt_id ");
		if(type != 255) {
			str.append("where n.nt_id=");
			str.append(type);
		}
		str.append(" ORDER BY n_ctime desc limit ");
		str.append((page-1) * count);
		str.append(",");
		str.append(count);
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Object> lists = new ArrayList<>();
		List<Object> list = null;
		Notice notice = null;
		Admin admin = null;
		Noticetype noticetype = null;
		try {
			conn = Conn.getConnect();
			ps = conn.prepareStatement(str.toString());
			rs = ps.executeQuery();

			while(rs.next()) {
				list = new ArrayList<>();
				notice = new Notice(
						rs.getInt("n_id"),
						rs.getString("n_title"),
						StringEscapeUtils.escapeHtml(rs.getString("n_content")),
						rs.getInt("n_view"),
						rs.getDate("n_ctime"),
						rs.getInt("nt_id"),
						rs.getInt("a_id"),
						rs.getString("n_filename"),
						rs.getString("n_filepath"),
						rs.getString("n_filexplain"));
				admin = new Admin(
						rs.getInt("a_id"),
						rs.getString("a_name"),
						rs.getString("a_loname"),
						null,
						rs.getString("a_explain"),
						rs.getString("a_logo"));
				noticetype = new Noticetype(
						rs.getInt("nt_id"),
						rs.getString("nt_name"),
						rs.getString("nt_explain"));
				list.add(notice);
				list.add(admin);
				list.add(noticetype);
				lists.add(list);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		} finally {
			Conn.close(rs, ps, conn);
		}
		return lists;
	}

	@Override
	public void updateNew(Notice notice) {
		Connection conn = null;
		PreparedStatement ps = null;
		String sql = "update notice set n_title=?,n_content=?,nt_id=?,a_id=?,n_from=? where n_id=?";
		try {
			conn = Conn.getConnect();
			ps =  conn.prepareStatement(sql);
			ps.setString(1, notice.getN_title());
			ps.setString(2, notice.getN_content());
			ps.setInt(3, notice.getNt_id());
			ps.setInt(4, notice.getA_id());
			ps.setString(5, notice.getN_from());
			ps.setInt(6, notice.getN_id());
			ps.executeUpdate();
		} catch (Exception e1) {
			e1.printStackTrace();
			throw new RuntimeException(e1.getMessage());
		} finally {
			Conn.close( null, ps, conn);
		}
	}

	public int addTeaminfo(Team team) {
		Connection conn = null;
		PreparedStatement ps = null;
		int flag = 0;
		String sql = "INSERT INTO team( te_id, te_name,te_logo, te_loname, te_passw, te_ctime,te_explain) VALUES(0, ?, ?, ?,?,?,?);";
		try {
			conn = Conn.getConnect();
			ps =  conn.prepareStatement(sql);
			ps.setString(1, team.getTe_name());
			ps.setString(2, team.getTe_logo());
			ps.setString(3, team.getTe_loname());
			ps.setString(4, team.getTe_passw());
			ps.setDate(1, (java.sql.Date) team.getTe_ctime());
			ps.setString(5, team.getTe_explain());
			flag = ps.executeUpdate();
		} catch (Exception e1) {
			e1.printStackTrace();
			throw new RuntimeException(e1.getMessage());
		} finally {
			Conn.close( null, ps, conn);
		}
		return flag;
		
		
	}

}
